﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;

namespace WPFExcelReport
{

    public class DeliveryNoteData : BaseFunction
    {

        byte[] b;
        string value;
        public DeliveryNoteData()
            : base()
        { 

       }
    /*    public DataSet LoadTableStructure(DataSet dsTarget)
        {
            try
            {
                TableQueue.Enqueue("WAFARBEN");
                TableQueue.Enqueue("SELECT WAFART as ArtNum,	WAFVAR as ArtVar,	 WAFZUSATZ as ArtColor,WAFBEZ as ColorDescription"
                    + "  FROM INTEX.WAFARBEN WHERE RowNum<1");
                 TableQueue.Enqueue("BSSATZ");
                 TableQueue.Enqueue("SELECT  "
                     + "BSKART"
                     + ",BSFDZ"
                     + ",BSSRFNR"
                     + ",BSFAKTOR"
                    + " FROM INTEX.BSSATZ WHERE RowNum<1");
                TableQueue.Enqueue("BASTKE");
                TableQueue.Enqueue("SELECT "
                              + "BAKART"
               + ",BAKVAR"
               + ",BAKFARBE"
               + ",BAKKART"
               + ",BAKKVAR"
               + ",BAKKFARBE"
               + ",BAKPATT"
               + ",BAKPOS"
               + ",BAKANZ"
               + ",BAKABF"
               + " FROM INTEX.BASTKE WHERE RowNum<1");
                TableQueue.Enqueue("BSFARB");
                TableQueue.Enqueue("SELECT  "
                    + "BSFKART"
                    + ",BSFKVAR"
                    + ",BSFKFARBE "
                    + ",BSFBEZ"
                + " FROM INTEX.BSFARB WHERE RowNum<1");
                TableQueue.Enqueue("BASTGA");
                TableQueue.Enqueue("SELECT "
                +"BAGFDZ"
                + ",BAGART"
                + ",BAGVAR"
                + ",BAGFARBE"
                + ",BAGPOS" 
                + ",BAGGART"
                + ",BAGGVAR"
                + ",BAGGFARBE"
                + ",BAGFDZ"
                + ",BAGKGNET"
                + ",BAGKGBRUT"
                +" FROM INTEX.BASTGA WHERE RowNum<1");
                TableQueue.Enqueue("WASTGA");
                TableQueue.Enqueue("SELECT  "
                       + "WAGART"
                + ",WAGVAR"
                + ",WAGWCOL"
                + ",WAGPSA"
                + ",WAGPOS"
                + ",WAGGART"
                + ",WAGGVAR"
                + ",WAGGFARBE"
                + ",WAGFDZ"
                + ",WAGKGNET"
                + ",WAGKGBRUT"
                + ",WAGEAFAKTOR"
                + ",WAGBEM"
                + " FROM INTEX.WASTGA WHERE RowNum<1"); 
                TableQueue.Enqueue("GSDAT");
                TableQueue.Enqueue("SELECT "
                + " GSART"
                + " ,GSVAR"
                + " ,GSMAT"
                + " ,GSNUMART"
                + " ,GSGARNNR"
                + " ,GSFACH"
                + " ,GSRECHNUM"
                + " ,GSRECHNR"
                 + " FROM INTEX.GSDAT WHERE RowNum<1"); 
                TableQueue.Enqueue("GASTGA");
                TableQueue.Enqueue("SELECT "
                    +"GAGART"
                    +",GAGVAR"
                    +",GAGFARBE"
                    +",GAGPOS"
                    +",GAGGART"
                    +",GAGGVAR"
                    +",GAGGFARBE"
                    +",GAGKGNET"
                    + ",GAGKGBRUT"   
                   + " FROM INTEX.GASTGA Where RowNum<1"); 
                TableQueue.Enqueue("GSCOLOR");
                TableQueue.Enqueue("SELECT GSCART,GSCVAR,GSCFARBE,GSCBEZ FROM INTEX.GSCOLOR WHERE RowNum<1");
                while (TableQueue.Count > 0)
                {
                    string TableName = TableQueue.Dequeue() as string;
                    string QueryStr = TableQueue.Dequeue() as string;
                    if (!dsTarget.Tables.Contains(TableName))
                    {
                        orlReadCommand = OracleDB.CreateCommand();
                        orlReadCommand.CommandText = QueryStr;
                        dsResult = Connect2Proxy(orlReadCommand);
                        if (dsResult.Tables.Count > 0)
                        {
                            dsResult.Tables[0].TableName = TableName;
                        }
                        foreach (DataTable dtTemp in dsResult.Tables)
                        {
                            dsTarget = ImportDataTable(dsTarget, dtTemp);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                 this.m_log_out.Error(ex);
            }
            return dsTarget;
        }*/
        public DeliveryNoteData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(DataSet dsTarget, DataRow dwWBBST)
        {
            string TableName = "WBBST";
            try
            { 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT rownum AS CODE,WBBRWLFD AS CARD,WBBPOS AS ITM,WBBSTKNR AS PIECENO,"
                                        + " WBBART AS ARTICLE,WBBVAR AS VAR,WBBFARBE AS COLOR,WBBCOLORIT AS COLORIT,"
                                        + " ROUND(WBBISTKLG/1000/0.9144,1) AS ACTYAND,ROUND(WBBIGEW/1000,1) AS ACTWEI,WBBURLIEFSCH,"
                                        + " ROUND(WBBIBRT/1000,0) AS WIDTH,WBBDPART AS LOT,WBBZENSUR||WBBZENSURKZ AS QUALITY,"
                                        + " WBBAUFKRS,WBBAUFLFD,WBBAUFPOS,WBBALIEFSCH,WBBTAX,SUBSTR(SDDATEN,2,10) AS DES, "
                                        + " substr(wbbbem,1,3) as WBBBEM "
                                        + " FROM WBBST ,SDDAT  WHERE WBBMANDANT = SUBSTR(SDKEY,1,2) "
                                        + " AND WBBTAX = SUBSTR(SDKEY,3,3) AND SDSA = '0201' AND SUBSTR(SDKEY,24,1) = 'V' "
                                        + " AND WBBMANDANT = '00' AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD =:WBBAUFLFD"
                                        + " AND WBBAUFPOS = :WBBAUFPOS AND WBBALIEFSCH = :WBBALIEFSCH AND WBBTAX = :WBBTAX "
                                        + " ORDER BY WBBBEM ";

                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwWBBST["WBBALIEFSCH"].ToString();
                orlReadCommand.Parameters.Add("WBBTAX", OracleType.NVarChar).Value = dwWBBST["WBBTAX"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    // Queue Q100 = new Queue();
                    // Q100.Enqueue("GAKG1");
                    // Q100.Enqueue("GAKG3");
                    // DivideBy100(dsResult.Tables[TableName], Q100);
                    dsResult.AcceptChanges();
                }
                foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                {
                    foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                    {
                        if (dc.ColumnName == "WBBTAX" )
                        {
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value;
                        }
                        if (dc.ColumnName == "DES")
                        {
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value;
                        }
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetCOLOR(DataSet dsTarget, DataRow dwWBBST)
        {
            string TableName = "COLOR";
            byte[] b;
            string value;
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDKEY,3,3) AS COLOR,SUBSTR(SDDATEN,2,10) AS DES, "
                                            + " SUBSTR(SDKEY,3,3) AS COLORSD"
                                            +" FROM WBBST ,SDDAT  WHERE WBBMANDANT = SUBSTR(SDKEY,1,2) "
                                            +" AND WBBTAX = SUBSTR(SDKEY,3,3) AND SDSA = '0201' AND SUBSTR(SDKEY,24,1) = 'V' "
                                            + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD =:WBBAUFLFD "
                                            + " AND WBBAUFPOS = :WBBAUFPOS AND WBBALIEFSCH = :WBBALIEFSCH ";
                               
                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwWBBST["WBBALIEFSCH"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    // Queue Q100 = new Queue();
                    // Q100.Enqueue("GAKG1");
                    // Q100.Enqueue("GAKG3");
                    // DivideBy100(dsResult.Tables[TableName], Q100);
                    dsResult.AcceptChanges();
                }
                foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                {
                    foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                    {
                         if(dc.ColumnName != "COLORSD")
                         {
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value;
                         }
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace,ex);
            }
            return dsTarget;
        }

        public DataSet GetVVTLIEF(DataSet dsTarget, DataRow dwWBBST)
        {
           
            string TableName = "VVTLIEF";
            byte[] b;
            string value;
            try
            { 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT VVTLIEFSCH,VVTLIEFSCHPOS,SUBSTR(SDDATEN,2,20) AS STATUS,VVVERSART,VVSPED,VVTRENR,"
                                        + " VVAVISNR,VVVERSDAT,round(VVTMTR/100/0.9144,3) AS QUANTITY,ROUND(VVTKG/1000,3) AS KG,"
                                        +"  VVTAUFKRS,VVTAUFLFD,VVTAUFPOS,'' AS WBBTAX"
                                        + " FROM VVTLIEF,VVLIEF,SDDAT  WHERE VVTMANDANT = VVMANDANT "
                                        +" AND VVTLIEFSCH = VVLIEFSCH AND VVTMANDANT = '00'"
                                        + " AND VVMANDANT = SUBSTR(SDKEY,1,2) AND VVSTATUS = SUBSTR(SDKEY,3,2) AND SDSA = '5555' "
                                        + " AND VVTAUFKRS = :VVTAUFKRS AND VVTAUFLFD = :VVTAUFLFD AND VVTAUFPOS = :VVTAUFPOS ";

                orlReadCommand.Parameters.Add("VVTAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("VVTAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("VVTAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName; 
                   // Queue Q100 = new Queue();
                   // Q100.Enqueue("GAKG1");
                   // Q100.Enqueue("GAKG3");
                   // DivideBy100(dsResult.Tables[TableName], Q100);
                    dsResult.AcceptChanges();
                }
                foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                {
                    foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                    {
                        if (dc.ColumnName == "STATUS")
                        {
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value;
                        }

                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet GetVKPOS(DataSet dsTarget, DataRow dwWBBST)
        {

            string TableName = "VKPOS";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT KDKUNDNA,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,"
                                                  + " VKPFARBE,VKPGROE,round(VKPMENGE1/100,3) AS QUANTITY,VKPMEEH,"
                                                  + " VKPKDBEZ,VKPKDARTNR,VKPKDFARBNR,VKPKDKOMM,VKPZUSATZ,KDNAME1"
                                                + " FROM VKPOS,KDSTAMM   WHERE VKPMANDANT = KDMANDANT "
                                                + " AND VKPKUNDNR = KDKUNDNR AND VKPMANDANT = '00' "
                                                + " AND TRIM(VKPSA)IS NULL AND VKPAUFART ='NA'AND VKPERL <>'J'"
                                                + " AND VKPAUFKRS = :VKPAUFKRS AND VKPAUFLFD = :VKPAUFLFD AND VKPAUFPOS = :VKPAUFPOS";

                orlReadCommand.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName; 
                }
                byte[] b;
                string value;
                foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                {
                    foreach (DataColumn dc in dsResult.Tables[TableName].Columns)
                    { 
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[dc.ColumnName] = value;

                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet BuildDeliveryNoteData(DataSet dsTarget, DataRow dwWBBST)
        {
            try
            {
                dsTarget = GetVKPOS(dsTarget, dwWBBST);
                dsTarget = GetVVTLIEF(dsTarget, dwWBBST);
                dsTarget = GetCOLOR(dsTarget, dwWBBST);
                dsTarget = GetWBBST(dsTarget, dwWBBST);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
    }
}